using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using FarPoint.Win.Spread.Data;
using FarPoint.Win.Spread;
using FarPoint.Win;
using FarPoint.Win.Spread.CellType;
using GrapeCity.Dashboard.OperationalModel;
using GrapeCity.Dashboard.Sparklines;
using Spread.Common.DataStore;
using System.Reflection;
using FarPoint.Win.Spread.Chart;

namespace GrapeCity.Dashboard
{
    public partial class Dashboard : Spread.Common.Features.Sample.BaseSampleControl
    {
        #region Fields
        private ProductSale _productSale = null;
        private ProductTarget _productTarget = null;
        private RegionalSale _regionalSale = null;
        private CustomerSale _customerSale = null;
        private CompanyShare _companyShare = null;
        private List<ProductSale> _productSaleList = null;
        private List<ProductTarget> _productTargetList = null;
        private List<RegionalSale> _regionalSaleList = null;
        private List<CustomerSale> _customerSaleListByTopRevenue = null;
        private List<CustomerSale> _customerSaleListByTopRegion = null;
        private List<CompanyShare> _companyShareList = null;
        private BulletChartSparklineGroup _bullGroup = null;
        private CurrencyCellType _currencyCellType = null;
        private PercentCellType _percentCellType = null;
        private List<string> _year = null;
        string[,] _monthHeadres = null;
        private Font _headerFont = null;
        private Font _columnHeaderFont = null;
        private string _selectedYr = "";
        private int _startCol = 0;
        private int _startRow = 2;
        #endregion
        #region ctor
        public Dashboard()
        {
            InitializeComponent();
            ObjectCreationAndLoad();
            this.fpSpread.ComboSelChange += FpSpread_ComboSelChange;
            this.Load += DashBoard_Load;
        }
        #endregion
        #region Event Handlers
        private void FpSpread_ComboSelChange(object sender, EditorNotifyEventArgs e)
        {
            string ctrlTxt = (e.EditingControl as FpCombo).SelectedItem.ToString();
            if (_selectedYr.Equals(ctrlTxt))
            {
                return;
            }
            _selectedYr = _year.IndexOf(ctrlTxt) >= 0 ? _year[_year.IndexOf(ctrlTxt)] : _selectedYr;
            fpSpread.Sheets[0].Cells[1, 24].Text = _selectedYr.ToString();
            fpSpread.SuspendLayout();
            UpdateDataInSpread();
            fpSpread.ResumeLayout();
        }

        private void DashBoard_Load(object sender, EventArgs e)
        {
            SetColumnsRowsVisibility();
            SetColor();
            SetFont_Alignment_Text();
            SetCellType();
            SetColWidthRowHeight();
            ProtectSheetToEnterValue();
            UpdateDataInSpread();
            BuildSparklines();
            BuildBorderOfChart();
            SetHeardersInChart();
            fpSpread.Sheets[0].RowCount = 46 + _customerSaleListByTopRegion.Count;
            fpSpread.Sheets[0].ColumnCount = 27;
        }
        #endregion
        #region Private methods
        private void ObjectCreationAndLoad()
        {
            var dashBoardItems = new DataService().DashBoardDataSource.LoadDashBoardItems();
            _productSale = new ProductSale(dashBoardItems.Invoices, dashBoardItems.Sales, dashBoardItems.Products);
            _productTarget = new ProductTarget(_productSaleList, dashBoardItems.TargetMasters);
            _regionalSale = new RegionalSale(dashBoardItems.Regions, dashBoardItems.Customers, dashBoardItems.Invoices);
            _customerSale = new CustomerSale(dashBoardItems.Customers, dashBoardItems.CustomerTargets, dashBoardItems.Invoices);
            _companyShare = new CompanyShare(dashBoardItems.Companies, dashBoardItems.MarketSales);
            _currencyCellType = new CurrencyCellType
            {
                MinimumValue = 0,
                CurrencySymbol = "$",
                ShowSeparator = true,
                DecimalPlaces = 2,
                PositiveFormat = CurrencyPositiveFormat.CurrencySymbolBefore

            };
            _percentCellType = new PercentCellType()
            {
                MinimumValue = 0,
                ShowSeparator = true,
                PercentSign = "%"
            };
            _headerFont = new Font("MS Sans Serif", 13, FontStyle.Bold);
            _columnHeaderFont = new Font("MS Sans Serif", 9, FontStyle.Bold);
            _monthHeadres = new string[,] { { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" } };
        }

        //Set visibility of headers and columns
        private void SetColumnsRowsVisibility()
        {
            fpSpread.Sheets[0].ColumnHeader.Visible = false;
            fpSpread.Sheets[0].RowHeader.Visible = false;
            fpSpread.Sheets[1].ColumnHeader.Visible = false;
            fpSpread.Sheets[1].RowHeader.Visible = false;
        }

        //Update data in spread
        private void UpdateDataInSpread()
        {
            _startCol = 0;
            _startRow = 2;
            int currentSelectedYear = Convert.ToInt32(_selectedYr);
            _productSaleList = _productSale.GetListbyYear(currentSelectedYear);
            _productTarget.UpdateProductSaleList(_productSaleList);
            _productTargetList = _productTarget.GetListByYear(currentSelectedYear);
            _regionalSaleList = _regionalSale.GetListByYear(currentSelectedYear);
            _customerSaleListByTopRevenue = _customerSale.GetListbyRevenue(currentSelectedYear);
            _customerSaleListByTopRegion = _customerSale.GetListbyRegionRevenue(currentSelectedYear);
            _companyShareList = _companyShare.GetListByYear(currentSelectedYear);
            fpSpread.ActiveSheet = fpSpread.Sheets[0];
            BindDataToSpread<ProductTarget>(_productTargetList, ref _startRow, ref _startCol, 6, "Key Matrics", _headerFont, _columnHeaderFont, true);
            _startRow += _productTargetList.Count + 3;
            _startCol = 0;
            BindDataToSpread<ProductSale>(_productSaleList, ref _startRow, ref _startCol, 6, "Product Sales", _headerFont, _columnHeaderFont, true);
            _startRow += _productSaleList.Count + 3;
            _startCol = 0;
            BindDataToSpread<RegionalSale>(_regionalSaleList, ref _startRow, ref _startCol, 4, "Region Revenue", _headerFont, _columnHeaderFont, true);
            _startRow += _regionalSaleList.Count + 3;
            _startCol = 0;
            BindDataToSpread<CustomerSale>(_customerSaleListByTopRevenue, ref _startRow, ref _startCol, 4, "Top Customer by Revenue", _headerFont, _columnHeaderFont);
            _startRow += _customerSaleListByTopRevenue.Count + 3;
            _startCol = 0;
            BindDataToSpread<CustomerSale>(_customerSaleListByTopRegion, ref _startRow, ref _startCol, 4, "Top Customer by Region", _headerFont, _columnHeaderFont);
            _startRow += _customerSaleListByTopRegion.Count + 3;
            _startCol = 0;
            BindDataToSpread<CompanyShare>(_companyShareList, ref _startRow, ref _startCol, 3, "Company Share in Market", _headerFont, _columnHeaderFont);
            SetFormulaForPercent(_startRow, 2, _companyShareList.Count + _startRow, 3);
        }
        //Bind data to a cell range
        private void BindDataToSpread<T>(List<T> list, ref int startRow, ref int startCol, int colCount, string dataHeader, Font headerFont, Font colHeaderFont, bool isAddedArray = false)
        {
            int _startRow = startRow;
            int _startCol = startCol;
            fpSpread.Sheets[1].Cells[startRow - 2, 0].Text = dataHeader;
            fpSpread.Sheets[1].AddSpanCell(startRow - 2, 0, 1, 30);
            fpSpread.Sheets[1].Rows[startRow - 2].Font = headerFont;
            fpSpread.Sheets[1].Rows[startRow - 2].BackColor = Color.FromArgb(30, 122, 197);
            fpSpread.Sheets[1].Rows[startRow - 2].ForeColor = Color.White;
            fpSpread.Sheets[1].Rows[startRow - 2].Height = 30;
            fpSpread.Sheets[1].Rows[startRow - 1].BackColor = Color.Gray;
            fpSpread.Sheets[1].Rows[startRow - 1].ForeColor = Color.White;
            fpSpread.Sheets[1].Rows[startRow - 1].Font = colHeaderFont;

            if (list.Count > 0)
            {
                // for data binding        
                using (SpreadDataBindingAdapter data = new SpreadDataBindingAdapter())
                {
                    foreach (PropertyInfo p in typeof(T).GetProperties())
                    {
                        string propertyName = p.Name;
                        if (colCount > _startCol)
                        {
                            fpSpread.Sheets[1].Cells[startRow - 1, _startCol].Text = propertyName;
                            _startCol++;
                        }

                    }
                    // Assign the list to datasource
                    data.DataSource = list;
                    data.Spread = fpSpread;
                    data.SheetName = "Data";
                    data.MapperInfo = new MapperInfo(startRow, startCol, list.Count, colCount);
                    data.FillSpreadDataByDataSource();
                    fpSpread.Sheets[1].Cells[startRow, startCol + 2, startRow + list.Count, startCol - 1 + colCount].CellType = _currencyCellType;
                    startCol = startCol + colCount;
                    if (isAddedArray)
                    {
                        fpSpread.Sheets[1].SetArray(_startRow - 1, startCol, _monthHeadres);
                        foreach (dynamic item in list)
                        {
                            fpSpread.Sheets[1].SetArray(_startRow, startCol, item.TwelveMonthsActual);
                            _startRow++;
                        }
                        fpSpread.Sheets[1].Cells[startRow, startCol, _startRow, startCol + 12].CellType = _currencyCellType;
                    }
                }
            }
            else
            {
                fpSpread.Sheets[0].AddSpanCell(0, 0, fpSpread.Sheets[0].RowCount, fpSpread.Sheets[0].ColumnCount);
                fpSpread.Sheets[0].Cells[0, 0].Text = "No record found.";
                fpSpread.Sheets[1].AddSpanCell(3, 0, fpSpread.Sheets[0].RowCount, fpSpread.Sheets[0].ColumnCount);
                fpSpread.Sheets[1].Cells[3, 0].Text = "No record found.";
            }
        }
        //Set formula for cell 
        private void SetFormulaForPercent(int startRow, int srcColumn, int endRow, int destColumn)
        {
            string columnHeader = fpSpread.Sheets[1].Columns[srcColumn].Label;
            for (int i = startRow; i < endRow; i++)
            {

                fpSpread.Sheets[1].Cells[i, destColumn].Formula = "" + columnHeader + (i + 1) + "/SUM(" + columnHeader + startRow + ":" + columnHeader + endRow + ")";
            }
            fpSpread.Sheets[1].Cells[startRow - 1, destColumn].Text = "Percentage";
            fpSpread.Sheets[1].Cells[startRow, destColumn, endRow, destColumn].CellType = _percentCellType;
            fpSpread.Sheets[1].Cells[startRow, destColumn, endRow, destColumn].Locked = true;
        }

        //Set color in sheet
        private void SetColor()
        {
            //  Set alternative row color
            fpSpread.Sheets[1].AlternatingRows.Count = 2;
            fpSpread.Sheets[1].AlternatingRows[0].BackColor = Color.FromArgb(246, 246, 246);
            fpSpread.Sheets[1].AlternatingRows[1].BackColor = Color.FromArgb(228, 228, 228);
            //Set color for sheet header
            fpSpread.Sheets[0].Rows[0, 2].BackColor = Color.FromArgb(30, 122, 197);
            fpSpread.Sheets[0].Rows[0, 2].ForeColor = Color.White;
            fpSpread.Sheets[0].Cells[1, 23, 1, 24].BackColor = Color.WhiteSmoke;
            fpSpread.Sheets[0].Cells[1, 23, 1, 24].ForeColor = Color.Black;
        }

        //Set font, alignment and text for cells
        private void SetFont_Alignment_Text()
        {
            fpSpread.Sheets[0].Cells[0, 1].Text = "MSC Sales Dashboard";
            fpSpread.Sheets[0].AddSpanCell(0, 1, 3, 20);
            fpSpread.Sheets[0].FrozenRowCount = 3;
            fpSpread.Sheets[0].Cells[1, 21].Text = "Select Year : ";
            fpSpread.Sheets[0].AddSpanCell(1, 21, 1, 3);
            fpSpread.Font = new Font("MS Sans Serif", 9, FontStyle.Regular);
            fpSpread.Sheets[0].Rows[0].Font = new Font("MS Sans Serif", 18, FontStyle.Bold);
            fpSpread.Sheets[0].Cells[1, 21, 1, 25].Font = _columnHeaderFont;
            fpSpread.Sheets[0].Rows[0].HorizontalAlignment = CellHorizontalAlignment.Left;
            fpSpread.Sheets[0].Cells[1, 21].HorizontalAlignment = CellHorizontalAlignment.Right;
            fpSpread.Sheets[0].Cells[1, 24].HorizontalAlignment = CellHorizontalAlignment.Center;
            fpSpread.Sheets[0].Cells[1, 24].Border = new LineBorder(Color.Black, 2);
            fpSpread.Sheets[0].Rows[2].HorizontalAlignment = CellHorizontalAlignment.Center;
            fpSpread.Sheets[0].Rows[0, fpSpread.Sheets[0].Rows.Count - 1].VerticalAlignment = CellVerticalAlignment.Top;
            fpSpread.Sheets[0].Cells[1, 0].HorizontalAlignment = CellHorizontalAlignment.Right;
            fpSpread.Sheets[0].Columns[1, 2].HorizontalAlignment = CellHorizontalAlignment.Right;
        }
        //Set combobox celltype
        private void SetCellType()
        {
            ComboBoxCellType comboBoxCellType = new ComboBoxCellType();
            _year = new List<string>();
            _year.AddRange(new string[]
            {
                DateTime.Now.Year+"",
                DateTime.Now.Year-1+""
            });
            if (DateTime.Now.Month < 12)
            {
                _year.Add(DateTime.Now.Year - 2 + "");
            }
            comboBoxCellType.Items = _year.ToArray();
            fpSpread.Sheets[0].Cells[1, 24].CellType = comboBoxCellType;
            _selectedYr = _year[0];
            comboBoxCellType.Editable = false;
            comboBoxCellType.MaxDrop = 4;
            fpSpread.Sheets[0].Cells[1, 24].Text = DateTime.Now.Year + "";
            fpSpread.Sheets[0].Cells[1, 24].Locked = false;
            fpSpread.Sheets[0].AddSpanCell(1, 24, 1, 2);
        }
        //Set row's height and column's width
        private void SetColWidthRowHeight()
        {
            //Column width
            fpSpread.Sheets[0].Columns[0].Width = 10;
            fpSpread.Sheets[0].Columns[1].Width = 15;
            fpSpread.Sheets[0].Columns[2].Width = 170;
            fpSpread.Sheets[0].Columns[3].Width = 35;
            fpSpread.Sheets[0].Columns[4].Width = 100;
            fpSpread.Sheets[0].Columns[5].Width = 15;
            fpSpread.Sheets[0].Columns[6].Width = 72;
            fpSpread.Sheets[0].Columns[7].Width = 15;
            fpSpread.Sheets[0].Columns[8].Width = 72;
            fpSpread.Sheets[0].Columns[9].Width = 15;
            fpSpread.Sheets[0].Columns[10].Width = 72;
            fpSpread.Sheets[0].Columns[11].Width = 15;
            fpSpread.Sheets[0].Columns[12].Width = 70;
            fpSpread.Sheets[0].Columns[13].Width = 10;
            fpSpread.Sheets[0].Columns[14].Width = 10;
            fpSpread.Sheets[0].Columns[15].Width = 20;
            fpSpread.Sheets[0].Columns[16].Width = 180;
            fpSpread.Sheets[0].Columns[17].Width = 15;
            fpSpread.Sheets[0].Columns[18].Width = 72;
            fpSpread.Sheets[0].Columns[19].Width = 15;
            fpSpread.Sheets[0].Columns[20].Width = 72;
            fpSpread.Sheets[0].Columns[21].Width = 15;
            fpSpread.Sheets[0].Columns[22].Width = 72;
            fpSpread.Sheets[0].Columns[23].Width = 15;
            fpSpread.Sheets[0].Columns[24].Width = 70;
            fpSpread.Sheets[0].Columns[25].Width = 10;
            fpSpread.Sheets[0].Columns[26].Width = 10;
            //row height
            fpSpread.Sheets[0].Rows[0].Height = 12;
            fpSpread.Sheets[0].Rows[1].Height = 25;
            fpSpread.Sheets[0].Rows[2].Height = 12;
            fpSpread.Sheets[0].Rows[4, 50].Height = 22;
            fpSpread.Sheets[0].Rows[3].Height = 15;
            fpSpread.Sheets[0].Rows[21].Height = 10;
            fpSpread.Sheets[0].Rows[38].Height = 10;

            //Sheet 1            
            fpSpread.Sheets[1].Columns[0, 19].Width = 100;
            fpSpread.Sheets[1].Columns[20, 21].Width = 1;
            fpSpread.Sheets[1].Columns[1].Width = 200;
        }
        //Protect sheet form edit
        private void ProtectSheetToEnterValue()
        {
            fpSpread.Sheets[0].Protect = true;
            fpSpread.Sheets[0].Cells[2, 0, fpSpread.Sheets[0].RowCount - 1, fpSpread.Sheets[0].ColumnCount - 1].Locked = true;
        }
        //Build chart sparklines
        private void BuildSparklines()
        {
            _startCol = 0;
            _startRow = 2;
            CreateSparklines<ProductTarget>(_productTargetList, _startRow, 0, 9, 2, 2, 5, 3, 4, -1, -1, true, true, true, true, false, true);
            fpSpread.Sheets[0].Cells[9, 11, 9 + _productTargetList.Count, 11].CellType = _currencyCellType;
            _startRow += _productTargetList.Count + 3;
            _startCol = 0;
            CreateSparklines<ProductSale>(_productSaleList, _startRow, 0, 27, 2, 4, 5, -1, -1, 2, 3, true, true, false, false, true, true);
            _startRow += _productSaleList.Count + 3;
            _startCol = 0;
            CreateSparklines<RegionalSale>(_regionalSaleList, _startRow, 0, 44, 2, 2, 3, -1, -1, -1, -1, true, true, false, false, false, true);
            fpSpread.Sheets[0].Cells[44, 11, 44 + _regionalSaleList.Count, 11].CellType = _currencyCellType;
            _startRow += _regionalSaleList.Count + 3;
            _startCol = 0;
            CreateSparklines<CustomerSale>(_customerSaleListByTopRevenue, _startRow, 0, 9, 15, -1, 3, 2, -1, -1, -1, false, true, true, false, false, false);
            fpSpread.Sheets[0].Cells[9, 23, 9 + _customerSaleListByTopRevenue.Count, 23].CellType = _currencyCellType;
            _startRow += _customerSaleListByTopRevenue.Count + 3;
            _startCol = 0;
            CreateSparklines<CustomerSale>(_customerSaleListByTopRegion, _startRow, 0, 44, 15, -1, 3, 2, -1, -1, -1, false, true, true, false, false, false);
            fpSpread.Sheets[0].Cells[44, 23, 44 + _customerSaleListByTopRegion.Count, 23].CellType = _currencyCellType;
            _startRow += _customerSaleListByTopRegion.Count + 3;
            _startCol = 0;
            CreateSparklines<CompanyShare>(_companyShareList, _startRow, 0, 27, 15, -1, -1, 2, -1, -1, -1, false, false, true, false, false, false);
            fpSpread.Sheets[0].Cells[26, 23, 26 + _companyShareList.Count, 23].CellType = _percentCellType;

        }
        private void CreateSparklines<T>(List<T> list,
            int srcRow,
            int srcCol,
            int destRow,
            int destCol,
            int targetColIndex = -1,
            int actualColIndex = -1,
            int goodColIndex = -1,
            int badcolIndex = -1,
            int targetUnitColIndex = -1,
            int actualUnitColIndex = -1,
            bool istargetShow = false,
            bool isActualShow = false,
            bool isGoodShow = false,
            bool isBadShow = false,
            bool isUnitPrinted = false,
            bool isArrayContains = false)
        {
            Double dbl = new Double();
            string header1 = this.fpSpread.Sheets[1].Columns[Convert.ToInt32(dbl.PositiveMin(targetColIndex, badcolIndex, actualColIndex, goodColIndex))].Label;
            string header2 = this.fpSpread.Sheets[1].Columns[Convert.ToInt32(dbl.Max(targetColIndex, badcolIndex, actualColIndex, goodColIndex))].Label;
            fpSpread.Sheets[1].Cells[srcRow, 20].Formula = "Max(" + header1 + "" + srcRow + ":" + header2 + "" + (srcRow + list.Count) + ")";
            header1 = this.fpSpread.Sheets[1].Columns[Convert.ToInt32(dbl.PositiveMin(targetUnitColIndex, actualUnitColIndex, -1, -1))].Label;
            header2 = this.fpSpread.Sheets[1].Columns[Convert.ToInt32(dbl.Max(targetUnitColIndex, actualUnitColIndex, -1, -1))].Label;
            fpSpread.Sheets[1].Cells[srcRow, 21].Formula = "Max(" + header1 + "" + srcRow + ":" + header2 + "" + (srcRow + list.Count) + ")";
            for (int i = 0; i < list.Count; i++)
            {
                if (isArrayContains)
                {
                    BindExcelSparklineInRow(i + srcRow, Convert.ToInt32(dbl.Max(targetColIndex, badcolIndex, actualColIndex, goodColIndex)) + 1, i + destRow, destCol);
                    DisplayText(i + srcRow, srcCol + 1, i + destRow, destCol + 2);
                    fpSpread.Sheets[0].AddSpanCell(i + destRow, destCol + 3, 1, 6);
                    BindSparklineInRow(i + srcRow,
                        srcCol - 1,
                        i + destRow,
                        destCol + 3,
                        targetColIndex,
                        actualColIndex,
                        goodColIndex,
                        badcolIndex,
                        actualUnitColIndex,
                        targetUnitColIndex,
                        srcRow,
                        istargetShow,
                        isActualShow,
                        isGoodShow,
                        isBadShow,
                        isUnitPrinted);
                    BindCircleSparklineInRow(i + srcRow, actualColIndex, i + destRow, destCol + 1, isUnitPrinted);
                    fpSpread.Sheets[0].AddSpanCell(i + destRow, destCol + 9, 1, 2);
                    DisplayText(i + srcRow, isUnitPrinted ? actualUnitColIndex : (actualColIndex > -1) ? actualColIndex : goodColIndex, i + destRow, destCol + 9);

                }
                else
                {
                    DisplayText(i + srcRow, srcCol + 1, i + destRow, destCol + 1);
                    fpSpread.Sheets[0].AddSpanCell(i + destRow, destCol + 2, 1, 6);
                    BindSparklineInRow(i + srcRow,
                        srcCol - 1,
                        i + destRow,
                        destCol + 2,
                        targetColIndex,
                        actualColIndex,
                        goodColIndex,
                        badcolIndex,
                        actualUnitColIndex,
                        targetUnitColIndex,
                        srcRow,
                        istargetShow,
                        isActualShow,
                        isGoodShow,
                        isBadShow,
                        isUnitPrinted);
                    fpSpread.Sheets[0].AddSpanCell(i + destRow, destCol + 8, 1, 2);
                    if (list[0] is CompanyShare)
                    {
                        goodColIndex = goodColIndex + 1;
                    }
                    DisplayText(i + srcRow, (actualColIndex > -1) ? actualColIndex : goodColIndex, i + destRow, destCol + 8);
                    if (list[0] is CompanyShare)
                    {
                        goodColIndex = goodColIndex - 1;
                    }
                }
            }
        }
        //Create circle sparkline based on the cell value
        private void BindCircleSparklineInRow(int srcRow, int srcCol, int destRow, int destCol, bool isUnitPrinted)
        {
            FarPoint.Win.Spread.ExcelSparklineGroup excelSparklineGroup = new FarPoint.Win.Spread.ExcelSparklineGroup();
            FarPoint.Win.Spread.ExcelSparklineSetting excelSparklineSetting = new FarPoint.Win.Spread.ExcelSparklineSetting();
            FarPoint.Win.Spread.ExcelSparkline excelSparkline = new FarPoint.Win.Spread.ExcelSparkline();
            this.fpSpread.ActiveSheet.SparklineContainer.Add(excelSparklineGroup);
            excelSparklineGroup.Renderer = new CircleSparkline(Color.Red, isUnitPrinted);
            excelSparklineGroup.Setting = excelSparklineSetting;
            string header1 = this.fpSpread.Sheets[1].Columns[srcCol - 1].Label;
            string header2 = this.fpSpread.Sheets[1].Columns[srcCol].Label;
            excelSparkline.Formula = "Data!" + header1 + "" + (srcRow + 1) + ":Data!" + header2 + "" + (srcRow + 1);
            excelSparkline.Column = destCol;
            excelSparkline.Row = destRow;
            excelSparklineGroup.Add(excelSparkline);
        }
        //Create month wise trend sparkline
        private void BindExcelSparklineInRow(int srcRow, int srcCol, int destRow, int destCol)
        {

            FarPoint.Win.Spread.ExcelSparklineGroup excelSparklineGroup = new ExcelSparklineGroup();
            FarPoint.Win.Spread.ExcelSparklineSetting excelSparklineSetting = new FarPoint.Win.Spread.ExcelSparklineSetting();
            FarPoint.Win.Spread.ExcelSparkline excelSparkline = new FarPoint.Win.Spread.ExcelSparkline();
            this.fpSpread.ActiveSheet.SparklineContainer.Add(excelSparklineGroup);
            excelSparklineGroup.Renderer = new LineSparklineRenderer();
            excelSparklineGroup.Setting = excelSparklineSetting;
            string header1 = this.fpSpread.Sheets[1].Columns[srcCol].Label;
            string header2 = this.fpSpread.Sheets[1].Columns[srcCol + 11].Label;
            excelSparkline.Formula = "Data!" + header1 + "" + (srcRow + 1) + ":Data!" + header2 + "" + (srcRow + 1);
            excelSparklineSetting.ShowNegative = true;
            excelSparkline.Column = destCol;
            excelSparkline.Row = destRow;
            excelSparklineGroup.Add(excelSparkline);
        }
        //Create custom sparkline for chart
        private void BindSparklineInRow(int srcRow,
            int srcCol,
            int destRow,
            int destCol,
            int targetColIndex = -1,
            int actualColIndex = -1,
            int goodColIndex = -1,
            int badcolIndex = -1,
            int actualUnitColIndex = -1,
            int targetUnitColIndex = -1,
            int maxUnitRevenueRowIndex = -1,
            bool istargetShow = false,
            bool isActualShow = false,
            bool isGoodShow = false,
            bool isBadShow = false,
            bool isUnitPrinted = false)
        {
            BulletChartSparklineSetting bulletSetting = new BulletChartSparklineSetting(Color.Gray);

            BulletChartSparklineGroup bulletGroup = new BulletChartSparklineGroup
            {
                Setting = bulletSetting
            };
            SheetCellRange actual = new SheetCellRange(this.fpSpread.Sheets[1], srcRow, actualColIndex > -1 ? actualColIndex : 0, 1, 1);
            SheetCellRange good = new SheetCellRange(this.fpSpread.Sheets[1], srcRow, goodColIndex > -1 ? goodColIndex : 0, 1, 1);
            SheetCellRange bad = new SheetCellRange(this.fpSpread.Sheets[1], srcRow, badcolIndex > -1 ? badcolIndex : 0, 1, 1);
            SheetCellRange target = new SheetCellRange(this.fpSpread.Sheets[1], srcRow, targetColIndex > -1 ? targetColIndex : 0, 1, 1);
            SheetCellRange actualUnit = new SheetCellRange(this.fpSpread.Sheets[1], srcRow, actualUnitColIndex > -1 ? actualUnitColIndex : 0, 1, 1);
            SheetCellRange targetUnit = new SheetCellRange(this.fpSpread.Sheets[1], srcRow, targetUnitColIndex > -1 ? targetUnitColIndex : 0, 1, 1);
            SheetCellRange maxUnit = new SheetCellRange(this.fpSpread.Sheets[1], maxUnitRevenueRowIndex > -1 ? maxUnitRevenueRowIndex : 0, 21, 1, 1);
            SheetCellRange maxRevenue = new SheetCellRange(this.fpSpread.Sheets[1], maxUnitRevenueRowIndex > -1 ? maxUnitRevenueRowIndex : 0, 20, 1, 1);

            BulletChartSparkline bulletSparkline = new BulletChartSparkline(this.fpSpread.Sheets[0],
                destRow,
                destCol,
                actual,
                target,
                good,
                bad,
                actualUnit,
                targetUnit,
                maxUnit,
                maxRevenue,
                istargetShow,
                isActualShow,
                isGoodShow,
                isBadShow,
                isUnitPrinted);
            if (_bullGroup == null)
            {
                _bullGroup = new BulletChartSparklineGroup
                {
                    Setting = bulletSetting
                };
                _bullGroup.Add(bulletSparkline);
                this.fpSpread.ActiveSheet.SparklineContainer.Add(_bullGroup);
            }
            else
            {
                _bullGroup.Add(bulletSparkline);
            }
        }
        //Set formula for cells in sheet[0] using the reference of sheet[1] "Data"
        private void DisplayText(int srcRow, int srcCol, int destRow, int destCol)
        {
            string header = this.fpSpread.Sheets[1].Columns[srcCol].Label;
            this.fpSpread.Sheets[0].Cells[destRow, destCol].Formula = "IF(Data!" + header + "" + (srcRow + 1) + " = " + "\"MESCIUS\",\"Our Company\",Data!" + header + "" + (srcRow + 1) + ")";
        }

        private void BuildBorderOfChart()
        {
            SetBorder(4, 1, 10 + _productTargetList.Count, 13);
            SetBorder(4, 15, 10 + _productTargetList.Count, 25);
            SetBorder(22, 1, 27 + _productSaleList.Count, 13);
            SetBorder(22, 15, 27 + _productSaleList.Count, 25);
            SetBorder(39, 1, 45 + _customerSaleListByTopRegion.Count, 25);
        }
        //Set border for charts
        private void SetBorder(int startRow, int startCol, int endRow, int endCol)
        {
            fpSpread.ActiveSheet.HorizontalGridLine = new GridLine(GridLineType.None);
            fpSpread.ActiveSheet.VerticalGridLine = new GridLine(GridLineType.None);
            fpSpread.ActiveSheet.Cells[startRow, startCol, endRow, endCol].Border = new LineBorder(Color.White, 2, true, true, true, true);
            fpSpread.ActiveSheet.Cells[startRow, startCol, endRow, startCol].Border = new LineBorder(Color.Gainsboro, 1, true, false, false, false);
            fpSpread.ActiveSheet.Cells[startRow, startCol, startRow, endCol].Border = new LineBorder(Color.Gainsboro, 1, false, true, false, false);
            fpSpread.ActiveSheet.Cells[startRow, endCol, endRow, endCol].Border = new LineBorder(Color.Gainsboro, 1, false, false, true, false);
            fpSpread.ActiveSheet.Cells[endRow, startCol, endRow, endCol].Border = new LineBorder(Color.Gainsboro, 1, false, false, false, true);
            fpSpread.ActiveSheet.Cells[startRow, startCol, startRow, startCol].Border = new LineBorder(Color.Gainsboro, 1, true, true, false, false);
            fpSpread.ActiveSheet.Cells[startRow, endCol, startRow, endCol].Border = new LineBorder(Color.Gainsboro, 1, false, true, true, false);
            fpSpread.ActiveSheet.Cells[endRow, endCol, endRow, endCol].Border = new LineBorder(Color.Gainsboro, 1, false, false, true, true);
            fpSpread.ActiveSheet.Cells[endRow, startCol, endRow, startCol].Border = new LineBorder(Color.Gainsboro, 1, true, false, false, true);

            fpSpread.ActiveSheet.Cells[startRow + 3, startCol + 1, startRow + 3, endCol - 1].Border = new LineBorder(Color.Gray, 1, false, false, false, true);
            fpSpread.ActiveSheet.Rows[startRow + 4].Height = 10;
            fpSpread.ActiveSheet.Rows[startRow].Height = 10;
            fpSpread.ActiveSheet.Rows[startRow + 1].Height = 10;
            fpSpread.ActiveSheet.Rows[startRow + 2].Height = 10;
        }
        //Set headers in all chart
        private void SetHeardersInChart()
        {
            Color actualColor = this.Darker(Color.Gray, 0);
            Color targetColor = this.Darker(Color.Gray, 100);
            Color badColor = this.Darker(Color.Gray, -50);
            Color goodColor = this.Darker(Color.Gray, -100);
            Color actualUnitColor = this.Darker(Color.Gray, -50);
            Color foreColor = Color.FromArgb(176, 152, 116);
            SetChartMainHeader(4, 2, 5, 7, 9, 11, -1, "Key Metrics", _headerFont, _columnHeaderFont, actualColor, targetColor, badColor, goodColor, actualUnitColor, foreColor);
            SetChartHeader(7, 2, "Month Wise Trends,Product,Actual vs Target,Actual($)", _columnHeaderFont, foreColor);
            SetChartMainHeader(4, 16, 21, 23, -1, -1, -1, "Top Customers", _headerFont, _columnHeaderFont, actualColor, goodColor, badColor, goodColor, actualUnitColor, foreColor);
            SetChartHeader(7, 16, "Customer,Actual vs Target,Actual($)", _columnHeaderFont, foreColor);
            SetChartMainHeader(22, 2, 7, 11, -1, -1, 9, "Product Sales", _headerFont, _columnHeaderFont, actualColor, targetColor, badColor, goodColor, actualUnitColor, foreColor);
            SetChartHeader(25, 2, "Month Wise Trends,Product,Revenue & Units vs Target,Unit", _columnHeaderFont, foreColor);
            SetChartMainHeader(22, 16, -1, -1, -1, -1, -1, "Product Sales", _headerFont, _columnHeaderFont, actualColor, targetColor, badColor, goodColor, actualUnitColor, foreColor);
            SetChartHeader(25, 16, "Company,% of Total Market,%", _columnHeaderFont, foreColor);
            SetChartMainHeader(39, 2, 9, 11, -1, -1, -1, "Revenue by Region", _headerFont, _columnHeaderFont, actualColor, targetColor, badColor, goodColor, actualUnitColor, foreColor);
            SetChartHeader(42, 2, "Month Wise Trends,Region,Actual vs Target,Actual($)", _columnHeaderFont, foreColor);
            SetChartMainHeader(39, 16, 21, 23, -1, -1, -1, "Top Customer by Region", _headerFont, _columnHeaderFont, actualColor, goodColor, badColor, goodColor, actualUnitColor, foreColor);
            SetChartHeader(42, 16, "Customer,Actual vs Target,Actual($)", _columnHeaderFont, foreColor);
        }
        private void SetChartHeader(int startRow, int StartCol, string headerStr, Font headerFont, Color foreColor)
        {
            string[] strings = headerStr.Split(',');
            CellHorizontalAlignment cellHorizontalAlignment = CellHorizontalAlignment.Left;
            foreach (string str in strings)
            {

                if ((strings.Count() > 3 && strings[2] == str) || (strings.Count() == 3 && strings[1] == str))
                {
                    cellHorizontalAlignment = CellHorizontalAlignment.Center;
                    fpSpread.Sheets[0].Cells[startRow, StartCol - 1].Text = str;
                    fpSpread.Sheets[0].Cells[startRow, StartCol - 1].HorizontalAlignment = cellHorizontalAlignment;
                    fpSpread.Sheets[0].Cells[startRow, StartCol - 1].Font = headerFont;
                    fpSpread.Sheets[0].Cells[startRow, StartCol - 1].ForeColor = foreColor;
                    fpSpread.Sheets[0].AddSpanCell(startRow, StartCol - 1, 1, 6);
                    StartCol += 6;
                }
                else
                {
                    fpSpread.Sheets[0].Cells[startRow, StartCol].Text = str;
                    fpSpread.Sheets[0].Cells[startRow, StartCol].HorizontalAlignment = cellHorizontalAlignment;
                    fpSpread.Sheets[0].Cells[startRow, StartCol].Font = headerFont;
                    fpSpread.Sheets[0].Cells[startRow, StartCol].ForeColor = foreColor;
                    StartCol += 2;
                }
            }
        }
        //Set main header in chart
        private void SetChartMainHeader(int startRow,
            int StartCol,
            int actualColIndex,
            int targetColIndex,
            int badColIndex,
            int goodColIndex,
            int actualUnitColIndex,
            string HeaderText,
            Font headerFont,
            Font subHeaderFont,
            Color actualColor,
            Color targetColor,
            Color badColor,
            Color goodColor,
            Color actualUnitColor,
            Color foreColor)
        {
            fpSpread.Sheets[0].Cells[startRow, StartCol].Text = HeaderText;
            fpSpread.Sheets[0].Cells[startRow, StartCol].ForeColor = foreColor;
            fpSpread.Sheets[0].Cells[startRow, StartCol].Font = headerFont;
            fpSpread.Sheets[0].AddSpanCell(startRow, StartCol, 3, 3);
            fpSpread.Sheets[0].Rows[startRow].Font = subHeaderFont;
            fpSpread.Sheets[0].Rows[startRow].HorizontalAlignment = CellHorizontalAlignment.Left;
            fpSpread.Sheets[0].Rows[startRow].CellPadding.Top = 2;
            if (actualColIndex > -1)
            {
                fpSpread.Sheets[0].Cells[startRow + 1, actualColIndex].BackColor = actualColor;
                fpSpread.Sheets[0].Cells[startRow, actualColIndex + 1].Text = "Actual";
                fpSpread.Sheets[0].AddSpanCell(startRow, actualColIndex + 1, 3, 1);
            }
            if (targetColIndex > -1)
            {
                fpSpread.Sheets[0].Cells[startRow + 1, targetColIndex].BackColor = targetColor;
                fpSpread.Sheets[0].Cells[startRow, targetColIndex + 1].Text = "Target";
                fpSpread.Sheets[0].AddSpanCell(startRow, targetColIndex + 1, 3, 1);
            }
            if (badColIndex > -1)
            {
                fpSpread.Sheets[0].Cells[startRow + 1, badColIndex].BackColor = badColor;
                fpSpread.Sheets[0].Cells[startRow, badColIndex + 1].Text = "Bad";
                fpSpread.Sheets[0].AddSpanCell(startRow, badColIndex + 1, 3, 1);
            }
            if (goodColIndex > -1)
            {
                fpSpread.Sheets[0].Cells[startRow + 1, goodColIndex].BackColor = goodColor;
                fpSpread.Sheets[0].Cells[startRow, goodColIndex + 1].Text = "Good";
                fpSpread.Sheets[0].AddSpanCell(startRow, goodColIndex + 1, 3, 1);
            }
            if (actualUnitColIndex > -1)
            {
                fpSpread.Sheets[0].Cells[startRow + 1, actualUnitColIndex].BackColor = actualUnitColor;
                fpSpread.Sheets[0].Cells[startRow, actualUnitColIndex + 1].Text = "Units";
                fpSpread.Sheets[0].AddSpanCell(startRow, actualUnitColIndex + 1, 3, 1);
            }

        }
        //Create a method for color calculation
        private Color Darker(Color color, int delta)
        {
            int r = color.R - delta;
            int g = color.G - delta;
            int b = color.B - delta;
            if (r > 0xff)
            {
                r = 0xff;
            }
            if (r < 0)
            {
                r = 0;
            }
            if (g > 0xff)
            {
                g = 0xff;
            }
            if (g < 0)
            {
                g = 0;
            }
            if (b > 0xff)
            {
                b = 0xff;
            }
            if (b < 0)
            {
                b = 0;
            }
            return Color.FromArgb(r, g, b);
        }
        #endregion
    }
}


//
//DashBoard.Designer.cs
//


namespace GrapeCity.Dashboard
{
    partial class Dashboard
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            FarPoint.Win.Spread.FlatScrollBarRenderer flatScrollBarRenderer1 = new FarPoint.Win.Spread.FlatScrollBarRenderer();
            FarPoint.Win.Spread.FlatScrollBarRenderer flatScrollBarRenderer2 = new FarPoint.Win.Spread.FlatScrollBarRenderer();
            this.fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None);
            this.fpSpread_Sheet1 = new FarPoint.Win.Spread.SheetView();
            this.fpSpread_Sheet2 = new FarPoint.Win.Spread.SheetView();
            this.pnlMain.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet2)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread);
            this.pnlMain.Size = new System.Drawing.Size(547, 574);
            // 
            // pnlControls
            // 
            this.pnlControls.Size = new System.Drawing.Size(181, 574);
            // 
            // fpSpread
            // 
            this.fpSpread.AccessibleDescription = "fpSpread, Data, Row 0, Column 0";
            this.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread.HorizontalScrollBar.Buttons = new FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton");
            this.fpSpread.HorizontalScrollBar.Name = "";
            flatScrollBarRenderer1.ArrowColor = System.Drawing.Color.FromArgb(((int)(((byte)(121)))), ((int)(((byte)(121)))), ((int)(((byte)(121)))));
            flatScrollBarRenderer1.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(255)))), ((int)(((byte)(255)))));
            flatScrollBarRenderer1.BorderActiveColor = System.Drawing.Color.FromArgb(((int)(((byte)(171)))), ((int)(((byte)(171)))), ((int)(((byte)(171)))));
            flatScrollBarRenderer1.TrackBarBackColor = System.Drawing.Color.FromArgb(((int)(((byte)(219)))), ((int)(((byte)(219)))), ((int)(((byte)(219)))));
            this.fpSpread.HorizontalScrollBar.Renderer = flatScrollBarRenderer1;
            this.fpSpread.Location = new System.Drawing.Point(0, 0);
            this.fpSpread.Name = "fpSpread";
            this.fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this.fpSpread_Sheet1,
            this.fpSpread_Sheet2});
            this.fpSpread.Size = new System.Drawing.Size(547, 574);
            this.fpSpread.TabIndex = 0;
            this.fpSpread.VerticalScrollBar.Buttons = new FarPoint.Win.Spread.FpScrollBarButtonCollection("BackwardLineButton,ThumbTrack,ForwardLineButton");
            this.fpSpread.VerticalScrollBar.Name = "";
            flatScrollBarRenderer2.ArrowColor = System.Drawing.Color.FromArgb(((int)(((byte)(121)))), ((int)(((byte)(121)))), ((int)(((byte)(121)))));
            flatScrollBarRenderer2.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(255)))), ((int)(((byte)(255)))), ((int)(((byte)(255)))));
            flatScrollBarRenderer2.BorderActiveColor = System.Drawing.Color.FromArgb(((int)(((byte)(171)))), ((int)(((byte)(171)))), ((int)(((byte)(171)))));
            flatScrollBarRenderer2.TrackBarBackColor = System.Drawing.Color.FromArgb(((int)(((byte)(219)))), ((int)(((byte)(219)))), ((int)(((byte)(219)))));
            this.fpSpread.VerticalScrollBar.Renderer = flatScrollBarRenderer2;
            this.fpSpread.ActiveSheetIndex = 1;
            // 
            // fpSpread_Sheet1
            // 
            this.fpSpread_Sheet1.Reset();
            this.fpSpread_Sheet1.SheetName = "Dashboard";
            // Formulas and custom names must be loaded with R1C1 reference style
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this.fpSpread_Sheet1.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // fpSpread_Sheet2
            // 
            this.fpSpread_Sheet2.Reset();
            this.fpSpread_Sheet2.SheetName = "Data";
            // Formulas and custom names must be loaded with R1C1 reference style
            this.fpSpread_Sheet2.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this.fpSpread_Sheet2.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet2.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet2.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet2.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet2.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet2.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet2.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet2.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet2.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet2.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread_Sheet2.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // DashBoard
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "DashBoard";
            this.Size = new System.Drawing.Size(729, 574);
            this.pnlMain.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet2)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread fpSpread;
        private FarPoint.Win.Spread.SheetView fpSpread_Sheet1;
        private FarPoint.Win.Spread.SheetView fpSpread_Sheet2;
    }
}


//
//BulletChartSparkline.cs
//


using System;
using System.Collections.Generic;
using System.Globalization;
using FarPoint.Win;
using FarPoint.Win.Spread.Chart;
using FarPoint.Win.Spread;
using System.Drawing;
using System.Runtime.Serialization;
using System.Xml;
using System.ComponentModel;
using System.Collections;
using FarPoint.Win.Spread.Model;
using GrapeCity.CalcEngine;

namespace GrapeCity.Dashboard.Sparklines
{
    [Serializable]
    public class BulletChartSparkline : ISparkline
    {
        // Fields
        private static List<WeakReference> __ENCList = new List<WeakReference>();
        private ISegmentData mBad;
        private int mColumn;
        private ISegmentData mGood;
        private ISegmentData mActual;
        private int mRow;
        private SheetView mSheetView;
        private ISegmentData mTarget;
        private ISegmentData mTargetUnit;
        private ISegmentData mActualUnit;
        private ISegmentData mMaxUnit;
        private ISegmentData mMaxRevenue;

        // Events
        public event PropertyTrackChangedEventHandler PropertyChanged;

        public event EventHandler Updated;

        // Methods
        public BulletChartSparkline()
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
        }

        public BulletChartSparkline(SheetView sheetView, 
            int row, 
            int column, 
            SheetCellRange actual, 
            SheetCellRange target, 
            SheetCellRange good, 
            SheetCellRange bad, 
            SheetCellRange actualUnit, 
            SheetCellRange targetUnit,
             SheetCellRange maxUnit,
            SheetCellRange maxRevenue,
            bool istargetShow = false, 
            bool isActualShow = false, 
            bool isGoodShow = false, 
            bool isBadShow = false, 
            bool isUnitPrinted=false)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            this.mSheetView = sheetView;
            this.mRow = row;
            this.mColumn = column;
            this.mActual = new CellRangeSegmentData(actual.SheetView, actual.Row, actual.Column, actual.RowCount, actual.ColumnCount, new ChartDataSetting());
            this.mTarget = new CellRangeSegmentData(target.SheetView, target.Row, target.Column, target.RowCount, target.ColumnCount, new ChartDataSetting());
            this.mGood = new CellRangeSegmentData(good.SheetView, good.Row, good.Column, good.RowCount, good.ColumnCount, new ChartDataSetting());
            this.mBad = new CellRangeSegmentData(bad.SheetView, bad.Row, bad.Column, bad.RowCount, bad.ColumnCount, new ChartDataSetting());
            this.mActualUnit = new CellRangeSegmentData(actualUnit.SheetView, actualUnit.Row, actualUnit.Column, actualUnit.RowCount, actualUnit.ColumnCount, new ChartDataSetting());
            this.mTargetUnit = new CellRangeSegmentData(targetUnit.SheetView, targetUnit.Row, targetUnit.Column, targetUnit.RowCount, targetUnit.ColumnCount, new ChartDataSetting());
            this.mMaxUnit = new CellRangeSegmentData(maxUnit.SheetView, maxUnit.Row, maxUnit.Column, maxUnit.RowCount, maxUnit.ColumnCount, new ChartDataSetting());
            this.mMaxRevenue = new CellRangeSegmentData(maxRevenue.SheetView, maxRevenue.Row, maxRevenue.Column, maxRevenue.RowCount, maxRevenue.ColumnCount, new ChartDataSetting());

            this.mActual.DataChanged += new SegmentDataChangedEventHandler(this.segmentData_DataChanged);
            this.mTarget.DataChanged += new SegmentDataChangedEventHandler(this.segmentData_DataChanged);
            this.mGood.DataChanged += new SegmentDataChangedEventHandler(this.segmentData_DataChanged);
            this.mBad.DataChanged += new SegmentDataChangedEventHandler(this.segmentData_DataChanged);
            this.mActualUnit.DataChanged += new SegmentDataChangedEventHandler(this.segmentData_DataChanged);
            this.mTargetUnit.DataChanged += new SegmentDataChangedEventHandler(this.segmentData_DataChanged);
            this.mMaxUnit.DataChanged += new SegmentDataChangedEventHandler(this.segmentData_DataChanged);
            this.mMaxRevenue.DataChanged += new SegmentDataChangedEventHandler(this.segmentData_DataChanged);

            this.IsActaulShow = isActualShow;
            this.IsBadShow = isBadShow;
            this.IsGoodShow = isGoodShow;
            this.IsTargetShow = istargetShow;
            this.IsUnitPrinted = isUnitPrinted;
        }

        public object Clone()
        {
            return new BulletChartSparkline(this.mSheetView, this.mRow, this.mColumn, (SheetCellRange)this.mActual, (SheetCellRange)this.mTarget, (SheetCellRange)this.mGood, (SheetCellRange)this.mBad, (SheetCellRange)this.mActualUnit, (SheetCellRange)this.mTargetUnit, (SheetCellRange)this.mMaxUnit, (SheetCellRange)this.mMaxRevenue);
        }

        public bool Deserialize(XmlNodeReader r)
        {
            if (!r.MoveToAttribute("row"))
                return false;
            Row = XmlConvert.ToInt32(r.Value);
            if (!r.MoveToAttribute("column"))
                return false;
            Column = XmlConvert.ToInt32(r.Value);

            if (!r.MoveToAttribute("actualFormula"))
                return false;
            actualFormula = r.Value;
            if (!r.MoveToAttribute("targetFormula"))
                return false;
            targetFormula = r.Value;
            if (!r.MoveToAttribute("goodFormula"))
                return false;
            goodFormula = r.Value;
            if (!r.MoveToAttribute("badFormula"))
                return false;
            badFormula = r.Value;
            if (!r.MoveToAttribute("targetUnitFormula"))
                return false;
            targetUnitFormula = r.Value;
            if (!r.MoveToAttribute("actualUnitFormula"))
                return false;
            actualUnitFormula = r.Value;
            if (!r.MoveToAttribute("maxUnitFormula"))
                return false;
            maxUnitFormula = r.Value;
            if (!r.MoveToAttribute("maxRevenueFormula"))
                return false;
            maxRevenueFormula = r.Value;
            return true;
        }

        private string actualFormula;
        private string targetFormula;
        private string goodFormula;
        private string badFormula;
        private string targetUnitFormula;
        private string actualUnitFormula;
        private string maxUnitFormula;
        private string maxRevenueFormula;

        public void ReloadFormula(FpSpread spread, SheetView ownerSheetView)
        {

            ArrayList dataModels = new ArrayList();
            foreach (SheetView sheet in spread.Sheets)
            {
                dataModels.Add(sheet.Models.Data);
            }
            if (!string.IsNullOrEmpty(actualFormula))
                mActual = ParseFormula(spread, actualFormula, false, null, null, dataModels);
             if (!string.IsNullOrEmpty(badFormula))
                mBad = ParseFormula(spread, badFormula, false, null, null, dataModels);
            if (!string.IsNullOrEmpty(goodFormula))
                mGood = ParseFormula(spread, goodFormula, false, null, null, dataModels);
            if (!string.IsNullOrEmpty(targetFormula))
                mTarget = ParseFormula(spread, targetFormula, false, null, null, dataModels);
            if (!string.IsNullOrEmpty(targetUnitFormula))
                mTargetUnit = ParseFormula(spread, targetUnitFormula, false, null, null, dataModels);
            if (!string.IsNullOrEmpty(actualUnitFormula))
                mActualUnit = ParseFormula(spread, actualUnitFormula, false, null, null, dataModels);
            if (!string.IsNullOrEmpty(maxUnitFormula))
                mMaxUnit = ParseFormula(spread, maxUnitFormula, false, null, null, dataModels);
            if (!string.IsNullOrEmpty(maxRevenueFormula))
                mMaxRevenue = ParseFormula(spread, maxRevenueFormula, false, null, null, dataModels);

        }
        private CellRangeSegmentData ParseFormula(FpSpread spread, string formula, bool useR1C1, Hashtable customFunctions, Hashtable customNames, ArrayList dataModels)
        {
            IValue value;
            Spreadsheet.Workbook _workbook = (Spreadsheet.Workbook)spread.AsWorkbook();
            SheetView sheet = null;
            int count = spread.Sheets.Count;
            CellRangeSegmentData data = null;

            FormulaEngine formulaParser = _workbook.FormulaEngine;
            formulaParser.EnterContext((IReferenceSource)_workbook.ActiveSheet, 0, 0);
            Expression expression;
            if (formulaParser.Parse(formula, out expression))
            {
                formulaParser.ExitContext();
                spread.ActiveSheet.EvaluateExpression(0, 0, ((IExpressionSupport2)spread.ActiveSheet.Models.Data).ParseFormula(0, 0, formula), out value);

                switch (value.ValueType)
                {
                    case GrapeCity.CalcEngine.ValueType.Reference:
                        var source = value.GetReferenceSource(null);
                        var reference = value.GetReference(null);
                        if (source != null)
                        {
                            for (int i = 0; i < count; i++)
                            {
                                if (source.Name.Equals(spread.Sheets[i].SheetName))
                                {
                                    sheet = spread.Sheets[i];
                                    break;
                                }
                            }
                            data = new CellRangeSegmentData(sheet, reference.Row, reference.Column, reference.Row2 - reference.Row + 1, reference.Column2 - reference.Column + 1, new ChartDataSetting());
                        }
                        break;
                    default:
                        break;
                }
            }
            else
            {
                formulaParser.ExitContext();
            }

            return data;
        }
        public bool Serialize(XmlTextWriter w)
        {
            try
            {
                w.WriteAttributeString("row", Row.ToString(CultureInfo.InvariantCulture));
                w.WriteAttributeString("column", Column.ToString(CultureInfo.InvariantCulture));
                CellRangeSegmentData bad = (CellRangeSegmentData)mBad;
                CellRangeSegmentData actual = (CellRangeSegmentData)mActual;
                CellRangeSegmentData good = (CellRangeSegmentData)mGood;
                CellRangeSegmentData target = (CellRangeSegmentData)mTarget;
                CellRangeSegmentData targetUnit = (CellRangeSegmentData)mTargetUnit;
                CellRangeSegmentData actualUnit = (CellRangeSegmentData)mActualUnit;
                CellRangeSegmentData maxUnit = (CellRangeSegmentData)mMaxUnit;
                CellRangeSegmentData maxRevenue = (CellRangeSegmentData)mMaxRevenue;

                badFormula = bad.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    bad.Row, bad.Column, bad.Row + bad.RowCount - 1, bad.Column + bad.ColumnCount - 1));

                actualFormula = actual.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    actual.Row, actual.Column, actual.Row + actual.RowCount - 1, actual.Column + actual.ColumnCount - 1));

                goodFormula = good.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    good.Row, good.Column, good.Row + good.RowCount - 1, good.Column + good.ColumnCount - 1));

                targetFormula = target.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    target.Row, target.Column, target.Row + target.RowCount - 1, target.Column + target.ColumnCount - 1));

                targetUnitFormula = targetUnit.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    targetUnit.Row, targetUnit.Column, targetUnit.Row + targetUnit.RowCount - 1, targetUnit.Column + targetUnit.ColumnCount - 1));

                actualUnitFormula = actualUnit.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    actualUnit.Row, actualUnit.Column, actualUnit.Row + actualUnit.RowCount - 1, actualUnit.Column + actualUnit.ColumnCount - 1));

                maxUnitFormula = maxUnit.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    maxUnit.Row, maxUnit.Column, maxUnit.Row + maxUnit.RowCount - 1, maxUnit.Column + maxUnit.ColumnCount - 1));

                maxRevenueFormula = maxRevenue.SheetName + "!" + FormulaEngine.UnparseA1Reference(new RangeReference(
                    maxRevenue.Row, maxRevenue.Column, maxRevenue.Row + maxRevenue.RowCount - 1, maxRevenue.Column + maxRevenue.ColumnCount - 1));

                w.WriteAttributeString("actualFormula", actualFormula);
                w.WriteAttributeString("targetFormula", targetFormula);
                w.WriteAttributeString("goodFormula", goodFormula);
                w.WriteAttributeString("badFormula", badFormula);
                w.WriteAttributeString("targetUnitFormula", goodFormula);
                w.WriteAttributeString("actualUnitFormula", badFormula);
                w.WriteAttributeString("maxUnitFormula", maxUnitFormula);
                w.WriteAttributeString("maxRevenueFormula", maxRevenueFormula);
            }
            catch
            {
                return false;
            }
            return true;
        }
        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("row", this.Row);
            info.AddValue("column", this.Column);
            info.AddValue("actual", this.Actual);
            info.AddValue("target", this.Target);
            info.AddValue("targetUnit", this.Target);
            info.AddValue("actualUnit", this.Target);
            info.AddValue("good", this.Good);
            info.AddValue("bad", this.Bad);
            info.AddValue("maxUnit", this.MaxUnit);
            info.AddValue("maxRevenue", this.MaxRevenue);
        }

        public object GetSparkLineData()
        {
            List<ISegmentData> values = new List<ISegmentData>
            {
                this.Actual,
                this.Target,
                this.Good,
                this.Bad,
                this.TargetUnit,
                this.ActualUnit,
                 this.MaxUnit,
                this.MaxRevenue
            };
            return values;
        }

        private void segmentData_DataChanged(object sender, SegmentDataChangedEventArgs e)
        {
            this.Updated?.Invoke(this, EventArgs.Empty);
        }



        // Properties

        public int Column
        {
            get
            {
                return this.mColumn;
            }
            set
            {
                int oldValue = this.mColumn;
                this.mColumn = value;
                this.PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Column", oldValue, value));
            }
        }
        public int Row
        {
            get
            {
                return this.mRow;
            }
            set
            {
                int oldValue = this.mRow;
                this.mRow = value;
                this.PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Row", oldValue, value));
            }
        }
        [CellRangeSegmentDataSupport("Actual")]
        public ISegmentData Actual
        {
            get
            {
                return this.mActual;
            }
            set
            {
                ISegmentData oldValue = this.mActual;
                this.mActual = value;
                this.PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Actual", oldValue, value));
            }
        }
        [CellRangeSegmentDataSupport("Target")]
        public ISegmentData Target
        {
            get
            {
                return this.mTarget;
            }
            set
            {
                ISegmentData oldValue = this.mTarget;
                this.mTarget = value;
                this.PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Target", oldValue, value));
            }
        }
       
        [CellRangeSegmentDataSupport("Good")]
        public ISegmentData Good
        {
            get
            {
                return this.mGood;
            }
            set
            {
                ISegmentData oldValue = this.mGood;
                this.mGood = value;
                this.PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Good", oldValue, value));
            }
        }
        [CellRangeSegmentDataSupport("Bad")]
        public ISegmentData Bad
        {
            get
            {
                return this.mBad;
            }
            set
            {
                ISegmentData oldValue = this.mBad;
                this.mBad = value;
                this.PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("Bad", oldValue, value));
            }
        }
        
        [CellRangeSegmentDataSupport("TargetUnit")]
        public ISegmentData TargetUnit
        {
            get
            {
                return this.mTargetUnit;
            }
            set
            {
                ISegmentData oldValue = this.mTargetUnit;
                this.mTargetUnit = value;
                this.PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("TargetUnit", oldValue, value));
            }
        }
        [CellRangeSegmentDataSupport("ActualUnit")]
        public ISegmentData ActualUnit
        {
            get
            {
                return this.mActualUnit;
            }
            set
            {
                ISegmentData oldValue = this.mActualUnit;
                this.mActualUnit = value;
                this.PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("ActualUnit", oldValue, value));
            }
        }
        [CellRangeSegmentDataSupport("MaxUnit")]
        public ISegmentData MaxUnit
        {
            get
            {
                return this.mMaxUnit;
            }
            set
            {
                ISegmentData oldValue = this.mMaxUnit;
                this.mMaxUnit = value;
                this.PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("MaxUnit", oldValue, value));
            }
        }
        [CellRangeSegmentDataSupport("MaxRevenue")]
        public ISegmentData MaxRevenue
        {
            get
            {
                return this.mMaxRevenue;
            }
            set
            {
                ISegmentData oldValue = this.mMaxRevenue;
                this.mMaxRevenue = value;
                this.PropertyChanged?.Invoke(this, new PropertyTrackChangedEventArgs("MaxRevenue", oldValue, value));
            }
        }

        public bool IsActaulShow { get; set; } = false;
        public bool IsTargetShow { get; set; } = false;
        public bool IsGoodShow { get; set; } = false;
        public bool IsBadShow { get; set; } = false;
        public bool IsUnitPrinted { get; set; } = false;


    }
    [Serializable]
    public class BulletChartSparklineGroup : ISparklineGroup, ISheetCellRangeSupport
    {
        // Fields
        private static List<WeakReference> __ENCList = new List<WeakReference>();
        private List<ISparkline> mList;
        private BulletChartSparklineRenderer mRenderer;
        private BulletChartSparklineSetting mSetting;

        // Events
        public event EventHandler ClearItems;

        public event ItemEventHandler ItemAdded;

        public event ItemEventHandler ItemRemoved;

        public event ItemEventHandler ItemUpdated;

        public event EventHandler Updated;

        // Methods
        public BulletChartSparklineGroup()
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            this.mRenderer = new BulletChartSparklineRenderer();
            this.mSetting = new BulletChartSparklineSetting();
            this.mList = new List<ISparkline>();
        }
     
        protected BulletChartSparklineGroup(SerializationInfo info, StreamingContext context)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            this.mRenderer = new BulletChartSparklineRenderer();
            this.mSetting = new BulletChartSparklineSetting();
            this.mList = new List<ISparkline>();
            this.mRenderer = (BulletChartSparklineRenderer)info.GetValue("renderer", typeof(BulletChartSparklineRenderer));
            this.mSetting = (BulletChartSparklineSetting)info.GetValue("setting", typeof(BulletChartSparklineSetting));
            this.mList = (List<ISparkline>)info.GetValue("list", typeof(List<BulletChartSparkline>));
        }

        protected BulletChartSparklineGroup(BulletChartSparklineRenderer renderer, BulletChartSparklineSetting setting, List<ISparkline> list)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            this.mRenderer = new BulletChartSparklineRenderer();
            this.mSetting = new BulletChartSparklineSetting();
            this.mList = new List<ISparkline>();
            this.mRenderer = renderer;
            this.mSetting = setting;
            this.mList.AddRange(list);
        }

        public void Add(ISparkline item)
        {
            this.mList.Add(item);
            item.Updated += new EventHandler(this.sparkline_Updated);
            this.ItemAdded?.Invoke(this, new ItemEventArgs(item));
        }

        public void Clear()
        {
            this.mList.Clear();
            this.ClearItems?.Invoke(this, EventArgs.Empty);
        }

        public object Clone()
        {
            return this.CloneSetting();
        }

        public ISparklineGroup CloneSetting()
        {
            return new BulletChartSparklineGroup(this.mRenderer, this.mSetting, this.mList);
        }

        public bool Contains(ISparkline item)
        {
            return this.mList.Contains(item);
        }

        public void CopyTo(ISparkline[] array, int arrayIndex)
        {
            this.mList.CopyTo(array, arrayIndex);
        }

        public bool Deserialize(XmlNodeReader r)
        {
            bool readNext = true;
            this.mList = new List<ISparkline>();
            object o;
            if (r.IsEmptyElement)
                return true;
            while (true)
            {
                if (readNext)
                {
                    if (!r.Read())
                        break;
                }
                else
                    readNext = true;
                switch (r.NodeType)
                {
                    case XmlNodeType.Element:
                        if (r.Name.Equals("renderer"))
                        {
                            o = Serializer.CreateObjectInstanceAndDeserialize(r, typeof(BulletChartSparklineRenderer));
                            if (o != null)
                                mRenderer = (BulletChartSparklineRenderer)o;
                            readNext = false;
                        }
                        else if (r.Name.Equals("setting"))
                        {
                            o = Serializer.CreateObjectInstanceAndDeserialize(r, typeof(BulletChartSparklineSetting));
                            if (o != null)
                                Setting = (BulletChartSparklineSetting)o;
                            readNext = false;
                        }
                        else if (r.Name.Equals("sparkline"))
                        {
                            o = Serializer.CreateObjectInstanceAndDeserialize(r, typeof(BulletChartSparkline));
                            if (o != null)
                                this.Add((BulletChartSparkline)o);
                            readNext = false;
                        }
                        break;
                }
            }
            return true;
        }

        public IEnumerator<ISparkline> GetEnumerator()
        {
            return (IEnumerator<ISparkline>)this.mList.GetEnumerator();
        }

        public IEnumerator GetEnumerator1()
        {
            return this.GetEnumerator();
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("renderer", this.mRenderer);
            info.AddValue("setting", this.mSetting);
            info.AddValue("list", this.mList);
        }

        public bool Remove(ISparkline item)
        {
            bool ret = this.mList.Remove(item);
            this.ItemRemoved?.Invoke(this, new ItemEventArgs(item));
            item.Updated -= new EventHandler(this.sparkline_Updated);
            return ret;
        }

        public bool Serialize(XmlTextWriter w)
        {
            w.WriteStartElement("renderer");
            w.WriteAttributeString("class", mRenderer.GetType().FullName);
            w.WriteAttributeString("assembly", mRenderer.GetType().Assembly.FullName);
            mRenderer.Serialize(w);
            w.WriteEndElement();

            w.WriteStartElement("setting");
            w.WriteAttributeString("class", mSetting.GetType().FullName);
            w.WriteAttributeString("assembly", mSetting.GetType().Assembly.FullName);
            mSetting.Serialize(w);
            w.WriteEndElement();
            int count = mList.Count;
            for (int i = 0; i < count; i++)
            {
                w.WriteStartElement("sparkline");
                w.WriteAttributeString("class", mList[i].GetType().FullName);
                w.WriteAttributeString("assembly", mList[i].GetType().Assembly.FullName);
                mList[i].Serialize(w);
                w.WriteEndElement();
            }
            return true;
        }

        private void sparkline_Updated(object sender, EventArgs e)
        {
            this.Updated?.Invoke(this, EventArgs.Empty);
        }

        // Properties
        public int Count
        {
            get
            {
                return this.mList.Count;
            }
        }

        public ISparkline this[int index]
        {
            get
            {
                if ((index < 0) || (index >= this.mList.Count))
                {
                    string[] VBt_arrayS0 = new string[] { "Invalid index: ", index.ToString(), " (must be between 0 and ", (this.mList.Count - 1).ToString(), ")" };
                    throw new IndexOutOfRangeException(string.Concat(VBt_arrayS0));
                }
                return this.mList[index];
            }
        }

        public ISparklineRenderer Renderer
        {
            get
            {
                return this.mRenderer;
            }
            set
            {
                if (!(value is BulletChartSparklineRenderer))
                {
                    throw new ArgumentException("Invalid renderer type - must be BulletChartSparklineRenderer");
                }
                this.mRenderer = (BulletChartSparklineRenderer)value;
                this.Updated?.Invoke(this, EventArgs.Empty);
            }
        }

        public ISparklineSetting Setting
        {
            get
            {
                return this.mSetting;
            }
            set
            {
                if (!(value is BulletChartSparklineSetting))
                {
                    throw new ArgumentException("Invalid setting type - must be BulletChartSparklineSetting");
                }
                this.mSetting = (BulletChartSparklineSetting)value;
                this.mSetting.PropertyChanged += new PropertyChangedEventHandler(mSetting_PropertyChanged);
                FireUpdated();
            }
        }
        void FireUpdated()
        {
            this.Updated?.Invoke(this, EventArgs.Empty);
        }
        void mSetting_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            FireUpdated();
        }

        public bool IsReadOnly
        {
            get
            {
                return false;
            }
        }

        #region IEnumerable Members

        IEnumerator IEnumerable.GetEnumerator()
        {
            return (IEnumerator<ISparkline>)this.mList.GetEnumerator();
        }

        #endregion

        #region ISheetCellRangeSupport Members

        public event ReloadSheetCellRangeEventHandler FormulaChanged;

        public void ReLoadFormula(FpSpread spread, SheetView ownerSheetView)
        {
            foreach (BulletChartSparkline sp in this)
            {
                sp.ReloadFormula(spread, ownerSheetView);
            }
        }

        public bool ReferenceError
        {
            get { return false; }
        }

        #endregion
    }

    [Serializable]
    public class BulletChartSparklineRenderer : ISparklineRenderer, ISerializable
    {
        // Methods
        public BulletChartSparklineRenderer()
        {
        }

        protected BulletChartSparklineRenderer(SerializationInfo info, StreamingContext context)
        {
        }

        private Color Darker(Color color, int delta)
        {
            int r = color.R - delta;
            int g = color.G - delta;
            int b = color.B - delta;
            if (r > 0xff)
            {
                r = 0xff;
            }
            if (r < 0)
            {
                r = 0;
            }
            if (g > 0xff)
            {
                g = 0xff;
            }
            if (g < 0)
            {
                g = 0;
            }
            if (b > 0xff)
            {
                b = 0xff;
            }
            if (b < 0)
            {
                b = 0;
            }
            return Color.FromArgb(r, g, b);
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
        }

        private void PaintHorizontalBulletChart(Graphics g, 
            Rectangle r, 
            Color ColorScheme, 
            int good, 
            int bad,  
            int actual, 
            int target,
            int actualUnit,
            int targetUnit,            
            double largestUnit = -1,
            double largestRevenue = -1,
            bool istargetShow = false, 
            bool isActualShow = false, 
            bool isGoodShow = false, 
            bool isBadShow = false,
            bool isUnitPrinted=true)
        {
            try
            {
                Double dbl = new Double();
                double maxValue = largestRevenue == -1 ? dbl.Max(good, bad, actual, target) : largestRevenue;
                double maxValueUnit = largestUnit == -1 ? dbl.Max(targetUnit, actualUnit, -1, -1) : largestUnit;
                double maxdiv = maxValue / r.Width;
                double maxdivUnit = maxValueUnit / r.Width;
                double actualPer = actual / maxdiv;
                double targetPer = target / maxdiv;
                double goodPer = good / maxdiv;
                double badPer = bad / maxdiv;
                double actualPerUnit = actualUnit / maxdivUnit;
                double targetPerUnit = targetUnit / maxdivUnit;
                Brush br = new SolidBrush(this.Darker(ColorScheme, -100));
                if (isGoodShow)
                {
                    g.FillRectangle(br, r.X, r.Y, Convert.ToInt32(Math.Ceiling(goodPer)), r.Height);
                }

                if (isBadShow)
                {
                    br = new SolidBrush(this.Darker(ColorScheme, -50));
                    g.FillRectangle(br, r.X, r.Y, Convert.ToInt32(Math.Ceiling(badPer)), r.Height);
                }
                if (isActualShow)
                {
                    if (isUnitPrinted)
                    {
                        br = new SolidBrush(this.Darker(ColorScheme, 0));
                        g.FillRectangle(br, r.X, r.Y, Convert.ToInt32(Math.Ceiling(actualPer)), r.Height / 2);
                        br = new SolidBrush(this.Darker(ColorScheme, -50));
                        g.FillRectangle(br, r.X, r.Y + r.Height / 2, Convert.ToInt32(Math.Ceiling(actualPerUnit)), r.Height / 2);
                    }
                    else
                    {
                        br = new SolidBrush(this.Darker(ColorScheme, 0));
                        g.FillRectangle(br, r.X, Convert.ToInt32(r.Y + (r.Height- (r.Height / 2)) / 2.0), Convert.ToInt32(Math.Ceiling(actualPer)), r.Height / 2);
                    }
                }
                if (istargetShow)
                {
                    if (isUnitPrinted)
                    {
                        br = new SolidBrush(this.Darker(ColorScheme, 100));
                        g.FillRectangle(br, r.X + Convert.ToInt32(Math.Ceiling(targetPer)) - 5, r.Y, 3, r.Height / 2);
                        g.FillRectangle(br, r.X + Convert.ToInt32(Math.Ceiling(targetPerUnit)) - 5, r.Y + r.Height / 2, 3, r.Height / 2);
                    }
                    else
                    {
                        br = new SolidBrush(this.Darker(ColorScheme, 100));
                        g.FillRectangle(br, r.X + Convert.ToInt32(Math.Ceiling(targetPer)) - 5, r.Y, 3, r.Height);
                    }
                }
            }
            catch { }

        }
      
        public void PaintSparkline(Graphics g, Rectangle rectangle, Sparkline sparkline)
        {
            BulletChartSparklineSetting setting = (BulletChartSparklineSetting)sparkline.Setting;
            BulletChartSparkline bulletChart = sparkline.Data as BulletChartSparkline;
            this.PaintHorizontalBulletChart(g, 
                rectangle, 
                setting.Color, 
                Convert.ToInt32(bulletChart.Good.GetValue(0)), 
                Convert.ToInt32(bulletChart.Bad.GetValue(0)), 
                Convert.ToInt32(bulletChart.Actual.GetValue(0)), 
                Convert.ToInt32(bulletChart.Target.GetValue(0)), 
                Convert.ToInt32(bulletChart.ActualUnit.GetValue(0)), 
                Convert.ToInt32(bulletChart.TargetUnit.GetValue(0)),
                Convert.ToDouble(bulletChart.MaxUnit.GetValue(0)),
                Convert.ToDouble(bulletChart.MaxRevenue.GetValue(0)),
                bulletChart.IsTargetShow, 
                bulletChart.IsActaulShow, 
                bulletChart.IsGoodShow, 
                bulletChart.IsBadShow,
                bulletChart.IsUnitPrinted);            
        }

        #region ISerializable Members

        public bool Serialize(System.Xml.XmlTextWriter w)
        {
            Serializer.SerializeString("BulletChartSparklineRenderer", "Name", w);
            return true;
        }
        public bool Deserialize(System.Xml.XmlNodeReader r)
        {
            return true;
        }
        #endregion
    }
    [Serializable]
    public class BulletChartSparklineSetting : ISparklineSetting
    {
        // Fields
        private static List<WeakReference> __ENCList = new List<WeakReference>();
        private Color mColor;

        // Events

        public event PropertyChangedEventHandler PropertyChanged;

        // Methods
        public BulletChartSparklineSetting()
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            this.mColor = Color.AliceBlue;
        }

        protected BulletChartSparklineSetting(SerializationInfo info, StreamingContext context)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            this.mColor = (Color)info.GetValue("color", typeof(Color));
        }

        public BulletChartSparklineSetting(Color color)
        {
            List<WeakReference> VBt_refL0 = __ENCList;
            lock (VBt_refL0)
            {
                __ENCList.Add(new WeakReference(this));
            }
            this.mColor = color;
        }

        public bool Deserialize(XmlNodeReader r)
        {
            ColorConverter con = new ColorConverter();
            if (!r.MoveToAttribute("Color"))
                return false;
            mColor = (Color)con.ConvertFromInvariantString(r.Value);           
            return true;
        }

        public void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            info.AddValue("color", this.Color);
        }

        public bool Serialize(XmlTextWriter w)
        {
            ColorConverter colorConverter = new ColorConverter();
            w.WriteAttributeString("Color", colorConverter.ConvertToString(this.Color));
            return true;
        }

        // Properties
        public Color Color
        {
            get
            {
                return this.mColor;
            }
            set
            {
                this.mColor = value;
                this.PropertyChanged?.Invoke(this, new PropertyChangedEventArgs("Color"));
            }
        }
    }

    [AttributeUsage(AttributeTargets.Property)]
    public class CellRangeSegmentDataSupportAttribute : Attribute
    {
        // Fields
        private bool _multiCell;
        private string _text;

        // Methods
        public CellRangeSegmentDataSupportAttribute(string text)
        {
            this._text = text;
        }

        public CellRangeSegmentDataSupportAttribute(string text, bool multi)
          : this(text)
        {
            this._multiCell = multi;
        }

        // Properties
        public bool MultiCell
        {
            get
            {
                return this._multiCell;
            }
        }

        public string Text
        {
            get
            {
                return this._text;
            }
        }
    }
}

//
//CircleSparkline.cs
//


using FarPoint.Win.Spread;
using FarPoint.Win.Spread.Chart;
using System;
using System.Drawing;

namespace GrapeCity.Dashboard.Sparklines
{
    class CircleSparkline : ExcelBaseSparkLineRenderer
    {
        Color _color;
        bool _isUnitPrinted;
        public CircleSparkline(Color color, bool isUnitPrinted)
        {

            this._color = color;
            this._isUnitPrinted = isUnitPrinted;
        }
        public override void PaintSparkline(Graphics g, Rectangle r, Sparkline sparkline)
        {
            var data = sparkline.Data;
            CellRangeSegmentData dataobj = data.GetSparkLineData() as CellRangeSegmentData;
            SheetView sv = dataobj.SheetView;
            int colStart = dataobj.Column;
            try
            {
                double target = Convert.ToDouble(sv.Cells[dataobj.Row, colStart].Value);
                double actaul = Convert.ToDouble(sv.Cells[dataobj.Row, colStart + 1].Value);
                double targetunit =  0;
                double actaulUnit = 0;
                if (_isUnitPrinted)
                {
                    targetunit = Convert.ToDouble(sv.Cells[dataobj.Row, colStart-2].Value);
                    actaulUnit = Convert.ToDouble(sv.Cells[dataobj.Row, colStart - 1].Value);

                }
                if (target > actaul || targetunit > actaulUnit)
                {
                    g.FillEllipse(new SolidBrush(_color), r.X+r.Width-r.Height-5,r.Y+2, r.Height-5, r.Height-5);
                }
            }
            catch
            {
               
            }
        }
    }
}
